package org.web.standardplatform.client.helper;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.wall.WallProvider;
import com.alibaba.druid.wall.spi.MySqlWallProvider;
import org.apache.commons.lang3.ObjectUtils;
import org.web.base.domain.ResultDO;
import org.web.base.domain.helper.ValidationHelper;


import java.lang.reflect.Method;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.web.standardplatform.client.domain.check.*;

import static org.web.standardplatform.client.domain.check.TableSqlCheckConstant.PARAM_ENUM.*;


public class TableSqlCheckHelper implements TableSqlCheckConstant {

    public static TableInfoDTO parseByTableType(String sql, DbType dbType) {
        TableInfoDTO tableInfoDTO = new TableInfoDTO();
        List<ColumnInfoDTO> columnInfoDTOList = new ArrayList<>();
        tableInfoDTO.setColumnInfoDTOList(columnInfoDTOList);
        String formatSQL = SQLUtils.format(sql, dbType);

        SQLStatementParser parser;
        if (dbType == DbType.mysql) {
            WallProvider mySqlWallProvider = new MySqlWallProvider();
            parser = mySqlWallProvider.createParser(formatSQL);
        } else {
            throw new RuntimeException("dbType 没有识别");
        }

        SQLCreateTableStatement sqlCreateTableStatement = parser.parseCreateTable();
        List<SQLObject> sqlObjects = sqlCreateTableStatement.getChildren();
        for (SQLObject sqlObject : sqlObjects) {
            if (sqlObject instanceof SQLColumnDefinition) {
                SQLColumnDefinition sqlColumnDefinition = (SQLColumnDefinition) sqlObject;
                ColumnInfoDTO columnInfoDTO = new ColumnInfoDTO(tableInfoDTO);
                columnInfoDTOList.add(columnInfoDTO);
                columnInfoDTO.setColumnName(sqlColumnDefinition.getColumnName().replaceAll("`", ""));
                if (ObjectUtils.isNotEmpty(sqlColumnDefinition.getComment())) {
                    columnInfoDTO.setColumnComment(sqlColumnDefinition.getComment().toString());
                }
                columnInfoDTO.setColumnType(sqlColumnDefinition.getDataType().getName());
                if (ObjectUtils.isNotEmpty(sqlColumnDefinition.getDataType().getArguments())) {
                    SQLExpr sqlExpr = sqlColumnDefinition.getDataType().getArguments().get(0);
                    if (sqlExpr instanceof SQLIntegerExpr) {
                        columnInfoDTO.setMaxSize(Integer.valueOf(sqlColumnDefinition.getDataType().getArguments().get(0).toString()));
                    }
                }

                if (ObjectUtils.isNotEmpty(sqlColumnDefinition.getConstraints())) {
                    List<String> constraints = new ArrayList<>();
                    columnInfoDTO.setConstraintList(constraints);
                    for (SQLColumnConstraint sqlColumnConstraint : sqlColumnDefinition.getConstraints()) {
                        if ("NOT NULL".equals(sqlColumnConstraint.toString())) {
                            columnInfoDTO.setAllowNull("N");
                        }
                        constraints.add(sqlColumnConstraint.toString());
                    }
                }
            }
        }
        // 提取每个ColumnInfoDTO的columnName
        tableInfoDTO.setColumnNameList(tableInfoDTO.getColumnInfoDTOList().stream().map(ColumnInfoDTO::getColumnName).collect(Collectors.toList()));
        if (sqlCreateTableStatement.getComment() != null) {
            String tableComment = sqlCreateTableStatement.getComment().toString();
            if (tableComment.startsWith("'")) {
                tableComment = tableComment.substring(1, tableComment.length() - 1);
            }
            if (tableComment.endsWith("'")) {
                tableComment = tableComment.substring(0, tableComment.length() - 1);
            }
            tableInfoDTO.setTableComment(tableComment);
        }
        tableInfoDTO.setTableName(sqlCreateTableStatement.getTableName().replaceAll("`", ""));
        tableInfoDTO.setPrimaryKeyList(sqlCreateTableStatement.getPrimaryKeyNames());
        tableInfoDTO.setCreateTableSql(formatSQL);
        tableInfoDTO.setTableType(dbType.toString());
        sqlCreateTableStatement.getPrimaryKeyNames();

        // 遍历表元素，查找外键
        parseForeignKeyColumnListByTableComment(tableInfoDTO);

        // 从表的描述信中，解析logicUniqueColumnList信息。
        tableInfoDTO.setLogicUniqueColumnList(parseColumnListByTableComment("逻辑唯一属性信息", tableInfoDTO.getTableComment()));
        // 如果指定了逻辑唯一信息，则需要进一步校验，属性信息是存在的，即在column的信息中，一定是存在的。
        if (!new HashSet<>(tableInfoDTO.getColumnNameList()).containsAll(tableInfoDTO.getLogicUniqueColumnList())) {
            throw new RuntimeException("逻辑唯一属性信息中，存在不存在的列名！");
        }

        // 从表的描述信中，解析logicUniqueColumnList信息。
        tableInfoDTO.setRedundancyColumnList(parseColumnListByTableComment("冗余属性信息", tableInfoDTO.getTableComment()));
        // 如果
        if (!new HashSet<>(tableInfoDTO.getColumnNameList()).containsAll(tableInfoDTO.getLogicUniqueColumnList())) {
            throw new RuntimeException("冗余属性信息中，存在不存在的列名！");
        }

        return tableInfoDTO;
    }

    public static void parseForeignKeyColumnListByTableComment(TableInfoDTO tableInfoDTO) {
        List<String> foreignKeyList = new ArrayList<>();
        List<String> foreignColumnList = new ArrayList<>();
        MySqlStatementParser parser = new MySqlStatementParser(tableInfoDTO.getCreateTableSql());
        SQLStatement statement = parser.parseStatement();

        if (statement instanceof MySqlCreateTableStatement) {
            MySqlCreateTableStatement createTable = (MySqlCreateTableStatement) statement;

            // Iterate through table elements
            List<SQLTableElement> tableElements = createTable.getTableElementList();
            for (SQLTableElement element : tableElements) {
                if (element instanceof SQLForeignKeyConstraint) {
                    SQLForeignKeyConstraint foreignKey = (SQLForeignKeyConstraint) element;
                    foreignKeyList.add(foreignKey.getName().getSimpleName().replaceAll("`", ""));
                    for (SQLName sqlName : foreignKey.getReferencingColumns()) {
                        foreignColumnList.add(sqlName.getSimpleName().replaceAll("`", ""));
                    }
                    // Extract foreign key details
//                    System.out.println("Foreign Key Columns: " + foreignKey.getReferencingColumns());
//                    System.out.println("Referenced Table: " + foreignKey.getReferencedTableName());
//                    System.out.println("Referenced Columns: " + foreignKey.getReferencedColumns());
                }
            }
            tableInfoDTO.setForeignKeyList(foreignKeyList);
            tableInfoDTO.setForeignColumnList(foreignColumnList);
        }
    }

    public static List<String> parseColumnListByTableComment(String prefix, String comment) {

        List<String> logicUniqueColumnList = new ArrayList<>();
        if (comment == null) {
            return logicUniqueColumnList;
        }
        // 正则表达式匹配 "逻辑唯一属性信息{}" 中的大括号内容
        Pattern pattern = Pattern.compile(prefix + "\\{([^}]+)}");
        Matcher matcher = pattern.matcher(comment);

        if (matcher.find()) {
            // 提取大括号内的内容
            String content = matcher.group(1);
            // 分组后按分号和逗号分割所有列
            String[] groups = content.split("[;,、]");
            for (String column : groups) {
                logicUniqueColumnList.add(column.trim());
            }
        }
        return logicUniqueColumnList;
    }

    public static List<TableInfoDTO> parseByTableType(List<String> sqlList) {
        return parseByTableType(sqlList, null);
    }

    public static List<TableInfoDTO> parseByTableType(List<String> sqlList, DbType dbType) {
        List<TableInfoDTO> list = new ArrayList<>();
        if (dbType == null) {
            dbType = DbType.mysql;
        }
        for (String sql : sqlList) {
            list.add(parseByTableType(sql, dbType));
        }
        return list;
    }

    /**
     * 按规则的维度，进行遍历校验。规则拆分为表级别和列级别的两组规则。
     */
    public static List<CheckRuleTaskDTO> checkBuildSql(List<CheckRuleDTO> checkRuleDTOList, TableInfoDTO tableInfoDTO) {
        List<CheckRuleTaskDTO> checkResultDTOList = new ArrayList<>();
        try {
            for (CheckRuleDTO checkRuleDTO : checkRuleDTOList) {
                if (checkRuleDTO.getRuleName().equals("Table.ForeignColumnList-column-allowNotNull")) {
                    System.out.println();
                }
                List<String> checkValueList = null;
                if (checkRuleDTO.getParamType().equals(TableSqlCheckConstant.PARAM_TYPE.TABLE.name())) {
                    checkValueList = getTableCheckValue(checkRuleDTO.getParamEnum(), tableInfoDTO);
                } else if (checkRuleDTO.getParamType().equals(TableSqlCheckConstant.PARAM_TYPE.COLUMN.name())) {
                    checkValueList = getColumnCheckValue(checkRuleDTO, tableInfoDTO);
                }
                if (checkValueList != null && !checkValueList.isEmpty()) {
                    // 只有失败的场合，才需要进行记录。
                    ResultDO<String> checkResult = hasValid(checkValueList, checkRuleDTO.getFunctionInfo());
                    if (checkResult.isFailed()) {
                        CheckRuleTaskDTO checkRuleTaskDTO = new CheckRuleTaskDTO();
                        checkRuleTaskDTO.setCheckRuleDTO(checkRuleDTO);
                        checkRuleTaskDTO.setFailReason(checkResult.getDomain());
                        checkResultDTOList.add(checkRuleTaskDTO);
                        checkRuleTaskDTO.setParams(checkValueList);
                    }
                }
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return checkResultDTOList;
    }


    /**
     * 校验CheckRuleDTO实体中，关于paramType和paramName是否合法。
     * 并且过滤状态为OFF的规则。
     */
    public static List<CheckRuleDTO> checkCheckRuleDTOListValid(List<CheckRuleDTO> checkRuleList) {
        List<CheckRuleDTO> filterList = checkRuleList.stream().filter(checkRuleDTO -> !TableSqlCheckConstant.STATUS.OFF.name().equalsIgnoreCase(checkRuleDTO.getRuleStatus())).collect(Collectors.toList());
        for (CheckRuleDTO checkRuleDTO : filterList) {
            boolean isValid = false;
            if (TableSqlCheckConstant.PARAM_TYPE.TABLE.name().equalsIgnoreCase(checkRuleDTO.getParamType())) {
                checkRuleDTO.setParamType(TableSqlCheckConstant.PARAM_TYPE.TABLE.name());
                for (String name : TABLE_PARAM_NAME_LIST) {
                    if (name.equalsIgnoreCase(checkRuleDTO.getParamName())) {
                        checkRuleDTO.setParamEnum(PARAM_ENUM_MAP.get(TableSqlCheckConstant.PARAM_TYPE.TABLE.name()).get(name));
                        checkRuleDTO.setParamName(name);
                        isValid = true;
                        break;
                    }
                }
            } else if (TableSqlCheckConstant.PARAM_TYPE.COLUMN.name().equalsIgnoreCase(checkRuleDTO.getParamType())) {
                String paramName = checkRuleDTO.getParamName();
                String columnParamName;
                String filterName;
                if (paramName.contains(".")) {
                    String[] array = paramName.split("\\.");
                    filterName = array[0];
                    columnParamName = array[1];
                    for (String name : TABLE_PARAM_NAME_LIST) {
                        if (name.equalsIgnoreCase(filterName)) {
                            filterName = name;
                            isValid = true;
                            break;
                        }
                    }
                    for (String name : COLUMN_PARAM_NAME_LIST) {
                        if (name.equalsIgnoreCase(columnParamName)) {
                            columnParamName = name;
                            isValid = true;
                            checkRuleDTO.setParamEnum(PARAM_ENUM_MAP.get(TableSqlCheckConstant.PARAM_TYPE.COLUMN.name()).get(name));
                            break;
                        }
                    }
                    checkRuleDTO.setParamName(filterName + "." + columnParamName);

                } else {
                    columnParamName = paramName;
                    for (String name : COLUMN_PARAM_NAME_LIST) {
                        if (name.equalsIgnoreCase(columnParamName)) {
                            checkRuleDTO.setParamEnum(PARAM_ENUM_MAP.get(TableSqlCheckConstant.PARAM_TYPE.COLUMN.name()).get(name));
                            columnParamName = name;
                            isValid = true;
                            break;
                        }
                    }
                    checkRuleDTO.setParamName(columnParamName);
                }
            }
            if (!isValid) {
                throw new RuntimeException("ParamType和ParamName组合不合法，ParamType:" + checkRuleDTO.getParamType() + ",ParamName:" + checkRuleDTO.getParamName());
            }
            ValidationHelper.validateWithThrow(CheckRuleDTO.class, checkRuleDTO);
        }
        return filterList;
    }


    public static ResultDO<String> hasValid(List<String> checkValueList, String checkRule) {
        ResultDO<String> resultDO = new ResultDO<>();
        String checkRuleFormat = checkRule.toLowerCase().trim();
        if (checkRuleFormat.startsWith(TableSqlCheckConstant.FUNCTION_TYPE.NotNull.name().toLowerCase())) {

            if (checkValueList == null || checkValueList.isEmpty()) {
                resultDO.setSuccess(false);
            }
            return resultDO;
        }
        if ("Include(smallint)".equalsIgnoreCase(checkRule)) {
            System.out.println();
        }
        String functionValue = getFunctionValue(checkRule);
        if (checkRuleFormat.startsWith(TableSqlCheckConstant.FUNCTION_TYPE.Pattern.name().toLowerCase())) {
            Pattern pattern = Pattern.compile(functionValue);
            for (String checkValue : checkValueList) {
                Matcher matcher = pattern.matcher(checkValue);
                if (!matcher.find()) {
                    resultDO.setDomain(checkValue);
                    resultDO.setSuccess(false);
                    return resultDO;
                }
            }
        } else if (checkRuleFormat.startsWith(TableSqlCheckConstant.FUNCTION_TYPE.Include.name().toLowerCase())) {
            String[] array = functionValue.split("\\|");
            for (String checkValue : checkValueList) {
                for (String s : array) {
                    if (checkValue.equalsIgnoreCase(s)) {
                        resultDO.setDomain(checkValue);
                        resultDO.setSuccess(true);
                        return resultDO;
                    }
                }
            }
            resultDO.setSuccess(false);
        } else if (checkRuleFormat.startsWith(TableSqlCheckConstant.FUNCTION_TYPE.NotInclude.name().toLowerCase())) {
            String[] array = functionValue.split("\\|");
            for (String checkValue : checkValueList) {
                for (String s : array) {
                    if (checkValue.equalsIgnoreCase(s)) {
                        resultDO.setDomain(checkValue);
                        resultDO.setSuccess(false);
                        return resultDO;
                    }
                }
            }
            resultDO.setSuccess(true);
        }
        return resultDO;
    }

    private static String getFunctionValue(String value) {
        int beginIndex = value.indexOf("(");
        int endIndex = value.indexOf(")");
        return value.substring(beginIndex + 1, endIndex);
    }

    public static List<String> getTableCheckValue(TableSqlCheckConstant.PARAM_ENUM paramEnum, TableInfoDTO tableInfoDTO) throws Exception {
        return getCheckValueListByTable(tableInfoDTO, paramEnum);
    }

    @SuppressWarnings("unchecked")
    private static List<String> getCheckValueListByTable(TableInfoDTO tableInfoDTO, TableSqlCheckConstant.PARAM_ENUM paramEnum) throws Exception {
        List<String> list = new ArrayList<>();
        Method method = GET_METHOD_MAP_OF_TableInfoDTO.get(paramEnum.getParamName());
        if (String.class == method.getReturnType()) {
            list.add((String) method.invoke(tableInfoDTO));
        } else {
            addList(list, (List<String>) method.invoke(tableInfoDTO));
        }
        return list;
    }

    @SuppressWarnings("unchecked")
    public static List<String> getColumnCheckValue(CheckRuleDTO checkRuleDTO, TableInfoDTO tableInfoDTO) throws Exception {
        List<String> list = new ArrayList<>();
        List<String> filterColumnNameList;
        String paramName = checkRuleDTO.getParamName();
        TableSqlCheckConstant.PARAM_ENUM paramEnum = checkRuleDTO.getParamEnum();

        String columnParam;
        if (paramName.contains(".")) {
            String[] array = paramName.split("\\.");
            Method method = GET_METHOD_MAP_OF_TableInfoDTO.get(array[0]);
            columnParam = array[1];
            filterColumnNameList = (List<String>) method.invoke(tableInfoDTO);
        } else {
            filterColumnNameList = tableInfoDTO.getColumnNameList();
            columnParam = checkRuleDTO.getParamName();
        }
        for (ColumnInfoDTO columnInfoDTO : tableInfoDTO.getColumnInfoDTOList()) {
            if (!filterColumnNameList.contains(columnInfoDTO.getColumnName())) {
                continue;
            }
            Method method = GET_METHOD_MAP_OF_ColumnInfoDTO.get(columnParam);
            list.add((String) method.invoke(columnInfoDTO));
        }
        return list;
    }

    private static void addList(List<String> list, List<String> addNewList) {
        if (addNewList != null) {
            list.addAll(addNewList);
        }
    }

    static Map<String, Method> GET_METHOD_MAP_OF_TableInfoDTO = new HashMap<>();
    static Map<String, Method> GET_METHOD_MAP_OF_ColumnInfoDTO = new HashMap<>();

    static {
        Method[] methods = TableInfoDTO.class.getMethods();
        for (Method method : methods) {
            if (method.getName().startsWith("get")) {
                GET_METHOD_MAP_OF_TableInfoDTO.put(method.getName().substring(3), method);
            }
        }
    }

    static {
        Method[] methods = ColumnInfoDTO.class.getMethods();
        for (Method method : methods) {
            if (method.getName().startsWith("get")) {
                GET_METHOD_MAP_OF_ColumnInfoDTO.put(method.getName().substring(3), method);
            }
        }
    }
}
